package persistence.impl;

import domain.LineItem;
import domain.Order;
import persistence.DBUtil;
import persistence.ItemDao;
import persistence.OrderDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl implements OrderDao {

//查询订单部分
    private static final String GET_ORDER_BY_USERID=
            "SELECT BILLADDR1 AS billAddress1,BILLADDR2 AS billAddress2, BILLCITY," +
                    "BILLCOUNTRY,BILLSTATE,BILLTOFIRSTNAME,BILLTOLASTNAME,BILLZIP," +
                    "SHIPADDR1 AS shipAddress1,SHIPADDR2 AS shipAddress2,SHIPCITY," +
                    "SHIPCOUNTRY,SHIPSTATE,SHIPTOFIRSTNAME,SHIPTOLASTNAME,SHIPZIP," +
                    "CARDTYPE,COURIER,CREDITCARD,EXPRDATE AS expiryDate,LOCALE,ORDERDATE," +
                    " ORDERS.ORDERID,TOTALPRICE,USERID AS userId FROM ORDERS" +
                    " WHERE ORDERS.USERID =?";

    public List<Order> getOrdersByUserId(String userid) {
        List<Order> orderList=new ArrayList<>();
        try{
            Connection connection=DBUtil.getConnection();
            PreparedStatement statement=connection.prepareStatement(GET_ORDER_BY_USERID);
            statement.setString(1,userid);
            ResultSet resultSet=statement.executeQuery();

            while (resultSet.next()){
                Order order=new Order();
                order.setBillAddress1(resultSet.getString("billAddress1"));
                order.setBillAddress2(resultSet.getString("billAddress2"));
                order.setBillCity(resultSet.getString("BILLCITY"));
                order.setBillCountry(resultSet.getString("BILLCOUNTRY"));
                order.setBillState(resultSet.getString("BILLSTATE"));
                order.setBillToFirstName(resultSet.getString("BILLTOFIRSTNAME"));
                order.setBillToLastName(resultSet.getString("BILLTOLASTNAME"));
                order.setBillZip(resultSet.getString("BILLZIP"));
                order.setShipAddress1(resultSet.getString("shipAddress1"));
                order.setShipAddress2(resultSet.getString("shipAddress2"));
                order.setShipCity(resultSet.getString("SHIPCITY"));
                order.setShipCountry(resultSet.getString("SHIPCOUNTRY"));
                order.setShipState(resultSet.getString("SHIPSTATE"));
                order.setShipToFirstName(resultSet.getString("SHIPTOFIRSTNAME"));
                order.setShipToLastName(resultSet.getString("SHIPTOLASTNAME"));
                order.setShipZip(resultSet.getString("SHIPZIP"));
                order.setCardType(resultSet.getString("CARDTYPE"));
                order.setCourier(resultSet.getString("COURIER"));
                order.setCreditcard(resultSet.getString("CREDITCARD"));
                order.setExprdate(resultSet.getString("expiryDate"));
                order.setLocale(resultSet.getString("LOCALE"));
                order.setOrderDate(resultSet.getDate("ORDERDATE"));
                order.setOrderid(resultSet.getInt("ORDERID"));
                order.setTotalprice(resultSet.getBigDecimal("TOTALPRICE"));
                order.setUserid(resultSet.getString("userId"));
                order.setStatus("P");
                orderList.add(order);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return orderList;
    }

    private static final String GET_ORDER_BY_ORDERID=
            "\t select BILLADDR1 AS billAddress1,BILLADDR2 AS billAddress2,BILLCITY,\n" +
                    "                    BILLCOUNTRY,BILLSTATE,BILLTOFIRSTNAME,BILLTOLASTNAME,BILLZIP,\n" +
                    "                    SHIPADDR1 AS shipAddress1,SHIPADDR2 AS shipAddress2,SHIPCITY,\n" +
                    "                    SHIPCOUNTRY,SHIPSTATE,SHIPTOFIRSTNAME,SHIPTOLASTNAME,SHIPZIP,\n" +
                    "                    CARDTYPE,COURIER,CREDITCARD,EXPRDATE AS expiryDate,LOCALE,\n" +
                    "                    ORDERDATE,ORDERS.ORDERID,TOTALPRICE,USERID AS userId,STATUS\n" +
                    "                    FROM ORDERS, ORDERSTATUS WHERE ORDERS.ORDERID =? AND ORDERS.ORDERID = ORDERSTATUS.ORDERID";

    public Order getOrderByOrderId(int orderId) {
        Order order=null;
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement statement=connection.prepareStatement(GET_ORDER_BY_ORDERID);
            statement.setInt(1,orderId);
            ResultSet resultSet=statement.executeQuery();
            if (resultSet.next()){
                order=new Order();
                order.setBillAddress1(resultSet.getString("billAddress1"));
                order.setBillAddress2(resultSet.getString("billAddress2"));
                order.setBillCity(resultSet.getString("BILLCITY"));
                order.setBillCountry(resultSet.getString("BILLCOUNTRY"));
                order.setBillState(resultSet.getString("BILLSTATE"));
                order.setBillToFirstName(resultSet.getString("BILLTOFIRSTNAME"));
                order.setBillToLastName(resultSet.getString("BILLTOLASTNAME"));
                order.setBillZip(resultSet.getString("BILLZIP"));
                order.setShipAddress1(resultSet.getString("shipAddress1"));
                order.setShipAddress2(resultSet.getString("shipAddress2"));
                order.setShipCity(resultSet.getString("SHIPCITY"));
                order.setShipCountry(resultSet.getString("SHIPCOUNTRY"));
                order.setShipState(resultSet.getString("SHIPSTATE"));
                order.setShipToFirstName(resultSet.getString("SHIPTOFIRSTNAME"));
                order.setShipToLastName(resultSet.getString("SHIPTOLASTNAME"));
                order.setShipZip(resultSet.getString("SHIPZIP"));
                order.setCardType(resultSet.getString("CARDTYPE"));
                order.setCourier(resultSet.getString("COURIER"));
                order.setCreditcard(resultSet.getString("CREDITCARD"));
                order.setExprdate(resultSet.getString("expiryDate"));
                order.setLocale(resultSet.getString("LOCALE"));
                order.setOrderDate(resultSet.getDate("ORDERDATE"));
                order.setOrderid(resultSet.getInt("ORDERID"));
                order.setTotalprice(resultSet.getBigDecimal("TOTALPRICE"));
                order.setUserid(resultSet.getString("userId"));
                order.setStatus(resultSet.getString("STATUS"));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(connection);

        }catch (Exception ex){
            ex.printStackTrace();
        }
        return order;
    }

//新建订单部分
    private static final String INSERT_ORDER=
            "INSERT INTO ORDERS (ORDERID, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE," +
                    "SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY," +
                    "COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, SHIPTOFIRSTNAME, SHIPTOLASTNAME," +
                    "CREDITCARD, EXPRDATE, CARDTYPE, LOCALE) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                    "?,?,?,?,?,?,?,?,?,?,?)";

    public void insertOrder(Order order) {
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement statement=connection.prepareStatement(INSERT_ORDER);
            statement.setInt(1,order.getOrderid());
            statement.setString(2, order.getUserid());
            java.sql.Timestamp date= new java.sql.Timestamp(order.getOrderDate().getTime());
            statement.setTimestamp(3,date);
            statement.setString(4,order.getShipAddress1());
            statement.setString(5,order.getShipAddress2());
            statement.setString(6,order.getShipCity());
            statement.setString(7,order.getShipState());
            statement.setString(8,order.getShipZip());
            statement.setString(9,order.getShipCountry());
            statement.setString(10,order.getBillAddress1());
            statement.setString(11,order.getBillAddress2());
            statement.setString(12,order.getBillCity());
            statement.setString(13,order.getBillState());
            statement.setString(14,order.getBillZip());
            statement.setString(15,order.getBillCountry());
            statement.setString(16,order.getCourier());
            statement.setBigDecimal(17,order.getTotalprice());
            statement.setString(18,order.getBillToFirstName());
            statement.setString(19,order.getBillToLastName());
            statement.setString(20,order.getShipToFirstName());
            statement.setString(21,order.getShipToLastName());
            statement.setString(22,order.getUserid());
            statement.setString(23,order.getExprdate());
            statement.setString(24,order.getCardType());
            statement.setString(25,order.getLocale());
            statement.executeUpdate();
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(connection);

        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

    private static final String INSERT_ORDER_STATUS=
            "INSERT INTO ORDERSTATUS (ORDERID, LINENUM, TIMESTAMP, STATUS)" +
                    "VALUES (?, ?,?, ?)";

    public void insertOrderStatus(Order order1) {
        try {
            Connection connection= DBUtil.getConnection();

            if (order1.getOrderDate() instanceof java.util.Date) //检查 order1.getOrderDate() 返回的对象是否是 java.util.Date 类型的实例
            {
                //将其转换为 java.sql.Date 类型
                java.sql.Date sqlDate = new java.sql.Date(((java.util.Date) order1.getOrderDate()).getTime());

                for (int i = 0; i < order1.getLineItems().size(); i++) {
                    PreparedStatement statement = connection.prepareStatement(INSERT_ORDER_STATUS);
                    statement.setInt(1, order1.getOrderid());
                    statement.setInt(2, order1.getLineItems().get(i).getLineNumber());
                    statement.setDate(3, sqlDate);
                    statement.setString(4, order1.getStatus());
                    statement.executeUpdate();
                    DBUtil.closeStatement(statement);
                }
            }else{
                System.out.println("OrderDaoImpl   时间转化有误");
            }
            DBUtil.closeConnection(connection);

        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

    private static final String CHECK_ORDERID_IS_EXIST=
            "SELECT * FROM ORDERS WHERE ORDERID =?";

    //为传入的Order对象生成一个不重复的随机订单ID
    public void setNotRepeatOrderId(Order order) {
        int id;
        ResultSet resultSet;

        try{
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement= connection.prepareStatement(CHECK_ORDERID_IS_EXIST);
            do {
                id=(int)(Math.random()*200+1);
                preparedStatement.setString(1, String.valueOf(id));
                resultSet= preparedStatement.executeQuery();
            }while (resultSet.next());//直到生成一个不在数据库中的id
            System.out.println(resultSet.next());
            order.setOrderid(id);//设置给order
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //直接在这里也完成对lineitem表的操作
    private static final String INSERT_ORDER_LINEITEM=
            "INSERT INTO LINEITEM (ORDERID,LINENUM,ITEMID,QUANTITY,UNITPRICE)" +
                    "VALUES(?,?,?,?,?)";

    public void insertOrderItem(Order order) {
        try{
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement= connection.prepareStatement(INSERT_ORDER_LINEITEM);
            List<LineItem> itemList=order.getLineItems();
            System.out.println("OrderDaoImpl  order.getLineItems().size=="+itemList.size());
            for(int i=0;i<itemList.size();i++){
                preparedStatement.setString(1, String.valueOf(order.getOrderid()));
                preparedStatement.setString(2, String.valueOf(i));
                preparedStatement.setString(3,itemList.get(i).getItemId());
                preparedStatement.setString(4, String.valueOf(itemList.get(i).getQuantity()));
                preparedStatement.setString(5, String.valueOf(itemList.get(i).getUnitPrice()));
                preparedStatement.executeUpdate();
            }DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //lineitem表中的查询操作
    private static final String GET_LINEITEM_BY_ORDERID=
            "SELECT ORDERID,LINENUM,ITEMID,QUANTITY,UNITPRICE FROM LINEITEM WHERE ORDERID=?";

    public List<LineItem> getLineItemByOrderId(int orderid) {
        List<LineItem> list=new ArrayList<LineItem>();
        ItemDao itemDao=new ItemDaoImpl();
        try{
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement= connection.prepareStatement(GET_LINEITEM_BY_ORDERID);
            preparedStatement.setInt(1,orderid);
            ResultSet resultSet= preparedStatement.executeQuery();
            while(resultSet.next()){
                LineItem lineItem=new LineItem();
                lineItem.setOrderId(orderid);
                lineItem.setItemId(resultSet.getString("itemid"));
                //System.out.println("OrderDaoImol 286 itemid="+resultSet.getString("itemid"));
                lineItem.setQuantity(resultSet.getInt("quantity"));
                lineItem.setUnitPrice(resultSet.getBigDecimal("unitprice"));
                lineItem.setItem(itemDao.getItem(resultSet.getString("itemid")));
                list.add(lineItem);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }


}
